﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using System.Threading.Tasks;
//using System.Data.SqlClient;
using System.ServiceModel;
using System.Runtime.Serialization;
using Database;
using System.IO;
using System.Data;
using System.Data.SqlServerCe;

namespace Database
{
    [DataContract]
    public class SQLInit
    {
      //  string path = @"Data Source=(LocalDB)\v11.0; AttachDbFilename= " + Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName + "\\Database\\RealDatabase.mdf ;Integrated Security=True";
        string path = @"Data Source=C:\Users\user\Desktop\Forum_V3\Database\Database.sdf";
        //string path = "Data Source=" + Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName + "\\Database\\Database.sdf";
   
        //        SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0; AttachDbFilename= C:\Users\user\Desktop\semesterB\sadna\סדנא מעודכן\Forum_v2\WCFService\ClassLibrary1\DataBaseNew\RealDatabase.mdf ;Integrated Security=True");
    
         
        //registerUser
        public User registerUser(int forumId, string uname, string name, string lastName, string password, string email)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c; 
            c.Open();
            cmd.CommandText = "INSERT INTO [User] (uname, name, lastName, password, email, forumId) " +
                        "Values ('" + uname + "', '" + name + "', '" + lastName + "', '" + password + "', '" + email + "', " + forumId + ")";
                      try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
            c.Close();
            return getUser(uname, forumId);
        }
        
        //admin ->will be the manager of the forum.
        //  int addForum(string title, User admin);
        public void addForum(string title, int forumId, string uname, string name, string lastName, string password, string email, int type)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create forum
            cmd.CommandText = "INSERT INTO [Forum] (id, title, admin, idForSubForums, type) " +
                        "Values (" + forumId + ", '" + title + "', '" + uname + "', 0, " + type + ")";
            try 
            { 
                cmd.ExecuteNonQuery(); 
            }
            catch (SqlCeException e) { }
          //  cmd.Clone();
            connection.Close();
            //register director to the forum
            User admin = getDirector();
            registerUser(forumId, admin.getUserName(), admin.getName(), admin.getLastName(), admin.getPassword(), admin.getEmail());
            //register admin to the forum
            registerUser(forumId, uname, name, lastName, password, email);
            updateDBIdforForums(forumId);

        }

        //addSubForum(int forumId, string title);
        public void addSubForum(int id, string title, int forumId, string uname) {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandText = "INSERT INTO [SubForum] (id, title, forumId, idForThreads) " +
                        "Values (" + id + ", '" + title + "', " + forumId + ", 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
            //cmd.Clone();
            connection.Close();
            //add moderator uname -(admin)
            addSubForumModerator(forumId, id, uname);
            //update idforSubForums in Forum +1
            updateFIdforSubForums(forumId, id);
            
        }

        //addSubForumModerator(int forumId, int subForumId, string uname);
        public bool addSubForumModerator(int forumId, int subForumId, string uname)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandText = "INSERT INTO [Moderator] (uname, forumId, subForumId, complaint) " +
                        "Values ('" + uname + "', " + forumId + ", " + subForumId + ", 0)";
            try
            {
                cmd.ExecuteNonQuery();
                connection.Close();
                return true;
            }
            catch (SqlCeException e) {
                connection.Close();
                return false;
            }
            //cmd.Clone();
           
        }
        
        //addThread(int forumId, int subForumId, string title, string body, User user);
        public void addThread(int id, int forumId, int subForumId, string title, string body, string uname)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create thread
            cmd.CommandText = "INSERT INTO [Thread] (id, title, body, creationTime, [user], forumId, subForumId, idForPosts) " +
                        "Values (" + id + " , '" + title + "', '" + body + "', '" + DateTime.Now + "', '" + uname + "', " + forumId + ", " + subForumId + ", 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
        //    cmd.Clone();
            connection.Close();
            //update idForThreads in subForum +1
            updateSFidForThreads(forumId, subForumId, id);
        }

        //int addPost(int forumId, int subForumId, int threadId, string title, string body, User user);
        public void addPost(int id, int forumId, int subForumId, int threadId, string title, string body, string uname)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT [Post] (id, title, body, creationTime, [user], forumId, subForumId, ThreadId) " +
                        "VALUES (" + id + ", '" + title + "', '" + body + "', '" + DateTime.Now.ToString() + "', '" + uname + "', " + forumId + ", " + subForumId + ", " + threadId + ")";
            try
            {
                cmd.ExecuteNonQuery();
            }
           catch (SqlCeException e) {  }
        //    cmd.Clone();  
            connection.Close();
            
            //TODO:: update idForPosts in thread +1
            updateTIdForPosts(forumId, subForumId, threadId, id);
          
        }

        //addDataBase(int id, string director)
        public void addDataBase(int id, string director)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandText = "INSERT INTO [DataBase] (id, director, idforForums) " +
                        "Values (" + id + ", '" + director + "', 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
          //  cmd.Clone();
            connection.Close();
        }

        //addDataBase(int id, string director)
        public bool addFriend(int forumId, string username, string friend)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //add friend
            cmd.CommandText = "INSERT INTO [UserFriends] (forimId, uname, friend) " +
                        "Values (" + forumId + ", '" + username + "', '" + friend + "')";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
        //    cmd.Clone();
            connection.Close();
            return true;
        }

        public void addForbiddenWord(int forumId, string word) {
            Console.WriteLine("try to add word  " + word);
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            
            cmd.CommandText = "INSERT INTO [ForbiddenWords] (forumId, word) " +
                        "Values (" + forumId + ", '" + word + "')";
            try
            {
                cmd.ExecuteNonQuery();
                Console.WriteLine("added!");
            }
            catch (SqlCeException e) { }
            //    cmd.Clone();
            connection.Close();
        }
///////////////////////////       Update Data     ///////////////////////////////////////////////////
        
        //update Thread Body
        public void updateThreadBody(int forum_id, int sub_forum_id, int threadId, string body)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update thread title
            cmd.CommandText = "UPDATE [Thread] set body = '" + body + "' WHERE id = " + threadId + "and forumId = " + forum_id + "and subForumId = " + sub_forum_id;
            cmd.ExecuteNonQuery();
        //    cmd.Clone();
            c.Close();
        }

        //update Thread Title
        public void updateThreadTitle(int forum_id, int sub_forum_id, int threadId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update thread title
            cmd.CommandText = "UPDATE [Thread] set title = '" + title + "' WHERE id = " + threadId + "and forumId = " + forum_id + "and subForumId = " + sub_forum_id;
            cmd.ExecuteNonQuery();
         //   cmd.Clone();
            c.Close();
        }
        //setSubForumTitle
        public void updateSubForumTitle(int forum_id, int subForumId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE [SubForum] set title = '" + title + "' WHERE id = " + subForumId + "and forumId = " + forum_id;
            cmd.ExecuteNonQuery();
         //   cmd.Clone();
            c.Close();
        }
        
        //setForumTitle
        public void updateForumTitle(int forumId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Forum] set title = '" + title + "' WHERE id = " + forumId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            c.Close();
        }
        
        public void updateDBIdforForums(int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE  [DataBase] set idforForums = " + num + "WHERE id = 1" ;
            cmd.ExecuteNonQuery();
           // cmd.Clone();
            connection.Close();
        }

        public void updateFIdforSubForums(int id, int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [Forum] set idForSubForums = " + num + "WHERE Id = "+ id;
            cmd.ExecuteNonQuery();
          //  cmd.Clone();
            connection.Close();
        }

        public void updateSFidForThreads(int ForumId, int subForumId, int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [SubForum] set idForThreads = " + num + "WHERE id = " + subForumId + "and forumId = " + ForumId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            connection.Close();
        }

        public void updateTIdForPosts(int ForumId, int SubForumId, int threadId, int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [Thread] set idForPosts = " + num + "WHERE id = " + threadId + "and forumId = " + ForumId + "and subForumId = " + SubForumId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            connection.Close();
        }

        /*
        public void updateForumAdmin()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [Forum] set admin = 'Ilya' WHERE id = " + 1;
            cmd.ExecuteNonQuery();
            cmd.Clone();
            connection.Close();
        }
        */

        //setPostTitle
        public void updatePostTitle(int forumId, int subForumId, int threadId, int postId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Post] set title = '" + title + "' WHERE id = " + postId +
                "and forumId = " + forumId + "and subForumId = " + subForumId + "and ThreadId = " + threadId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            c.Close();
        }
        //setPostBody
        public void updatePostBody(int forumId, int subForumId, int threadId, int postId, string body)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Post] set body = '" + body + "' WHERE id = " + postId +
                "and forumId = " + forumId + "and subForumId = " + subForumId + "and ThreadId = " + threadId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            c.Close();
        }

        //setModeratorComplaints
        public void updateModeratorComplaints(int forumId, int subForumId, string moderator, int complaint)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Moderator] set complaint = '" + complaint + "' WHERE forumId = " + forumId +
                "and subForumId = " + subForumId + "and uname = '" + moderator + "'";
            cmd.ExecuteNonQuery();
           // cmd.Clone();
            c.Close();
        }

/// /////////////////////////////////////       Read Data         ///////////////////////////

        /*-
        public DBmediator initDataBase(DBmediator db){
      
       //     Console.WriteLine("Trying to connect to database");
   //         Console.WriteLine(path);
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();    
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Forum";
            cmd.Connection = connection;
            connection.Open();
            dr1 = cmd.ExecuteReader();
         //   Console.WriteLine("Connected to database!");
            //add forums
            while (dr1.Read()) {
            //    Console.WriteLine("111");
                User admin = getUser((string)dr1["admin"], (int)dr1["id"]);
                int type = (int)dr1["type"];
                db.setIdForForums((int)dr1["id"] - 1);
                db.addForum((string)dr1["title"], admin, type);
                
            }
            dr1.Close();
            //cmd.Clone();
   //         Console.WriteLine("Connected to database!");
            //add users
            cmd.CommandText = "SELECT * from [User]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int forumId = (int)dr1["forumId"];
                string uname = (string)dr1["uname"];
                string name = (string)dr1["name"];
                string lastName = (string)dr1["lastName"];
                string password = (string)dr1["password"];
                string email = (string)dr1["email"];

                if (!uname.Equals(db.getForumAdmin(forumId).getUserName()))
                    db.registerUser(forumId, uname, name, lastName, password, email);
            //    cmd.Clone();
            }
            dr1.Close();

            //add subforums
            cmd.CommandText = "select * from [SubForum]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read()) {
            int f_id = (int)dr1["forumId"];
            int sf_id = (int)dr1["id"];
            string title = (string)dr1["title"];
            db.setIdForSubForums(f_id, sf_id - 1);
            db.addSubForum(f_id, title);

          //  cmd.Clone();
            }
            dr1.Close();

            // add moderators
            cmd.CommandText = "select * from [Moderator]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                string uname = (string)dr1["uname"];
                int forumId = (int)dr1["forumId"];
                int subForumId = (int)dr1["subForumId"];
                int complaint = (int)dr1["complaint"];           
                User user = getUser(uname, forumId);
                db.addSubForumModerator(forumId, subForumId, uname);
                db.setModeratorComplaints(forumId, subForumId, uname, complaint);
     //           cmd.Clone();
            }
            dr1.Close();

            //add threads
            cmd.CommandText = "select * from [Thread]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int thread_id = (int)dr1["id"];
                string title = (string)dr1["title"];
                string body = (string)dr1["body"];
                DateTime creationTime = Convert.ToDateTime((string)dr1["creationTime"]);
                string uname = (string)dr1["user"];
                int forumId = (int)dr1["forumId"];
                int subForumId = (int)dr1["subForumId"];
                db.setIdForThreads(forumId, subForumId, thread_id - 1);
                User user = getUser(uname, forumId);
                db.addThread(forumId, subForumId, title, body, user);
       //         cmd.Clone();
            }
            dr1.Close();
            
            //add post
            cmd.CommandText = "select * from [Post]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int postId = (int)dr1["id"];
                string title = (string)dr1["title"];
                string body = (string)dr1["body"];
                DateTime creationTime = Convert.ToDateTime((string)dr1["creationTime"]);
                string uname = (string)dr1["user"];
                int forumId = (int)dr1["forumId"];
                int subForumId = (int)dr1["subForumId"];
                int thread_id = (int)dr1["ThreadId"];
                User user = getUser(uname, forumId);
                db.setIdForPosts(forumId, subForumId, thread_id, postId - 1);
                db.addPost(forumId, subForumId, thread_id, title, body, user);
            }
            dr1.Close();
            

            //add friends
            cmd.CommandText = "select * from [UserFriends]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int f_id = (int)dr1["forimId"];
                string uname = (string)dr1["uname"];
                string friend = (string)dr1["friend"];
                db.addFriend(f_id, uname, friend);
       //         cmd.Clone();
            }
            dr1.Close();

            connection.Close();
            return db;

       } */

        public User getUser(string name, int forum_id) {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeDataReader dr;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c; 
            c.Open();
            cmd.CommandText = "select * from [User] WHERE (uname = '" + name + "' and forumId = " + forum_id + ")";
            dr = cmd.ExecuteReader();
            if (dr.Read()){
                User user = new User((string)dr["uname"],(string)dr["name"], (string)dr["lastName"], (string)dr["password"], (string)dr["email"]);
                c.Close();
                dr.Close();
                return user;
            }   
            else{     
            //TODO:: add friends
           // cmd.Clone();
                c.Close();
                return null;
            }
        }

        public User getDirector()
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeDataReader dr;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            cmd.CommandText = "select * from [Admin]";
            dr = cmd.ExecuteReader();
            dr.Read();
            User admin = new User((string)dr["uname"],(string)dr["name"], (string)dr["lastName"], (string)dr["password"], (string)dr["email"]);            

            //TODO:: add friends
            // cmd.Clone();
            c.Close();
            return admin;
     
        }
        
///////////////////////////        Delete        //////////////////////////
        public bool removeSubForumModerator(int forum_id, int sub_forum_id, string u_name)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            cmd.CommandText = "delete from [Moderator] WHERE forumId = " + forum_id + " and subForumId = " + sub_forum_id + " and uname = '" + u_name + "'"; 
            cmd.ExecuteReader();   
           // cmd.Clone();
            c.Close();
            return true;
        }

        public bool removeThread(int forum_id, int sub_forum_id, int thread_id)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            SqlCeDataReader dr;
            cmd.Connection = c;
            c.Open();
            //remove thread
            cmd.CommandText = "delete from [Thread] WHERE id = " + thread_id + " and forumId = " + forum_id + " and subForumId = " + sub_forum_id;
            dr = cmd.ExecuteReader();
            //cmd.Clone();
            dr.Close();
            // remove posts  
            cmd.CommandText = "delete from [Post] WHERE forumId = " + forum_id + " and subForumId = " + sub_forum_id + " and threadId = " + thread_id;
            cmd.ExecuteReader();
            //cmd.Clone();
            dr.Close();
            c.Close();
            return true;
            
        }
        
        public bool removePost(int forum_id, int sub_forum_id, int thread_id, int post_id)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            // remove posts
            cmd.CommandText = "delete from [Post] WHERE id = " + post_id + "and forumId = " + forum_id + " and subForumId = " + sub_forum_id + " and threadId = " + thread_id;
            cmd.ExecuteReader();
           // cmd.Clone();
            c.Close();
            return true;
        }

        public bool removeFriend(int forumId, string username, string friend)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            // remove friend
            cmd.CommandText = "delete from [UserFriends] WHERE forimId = " + forumId + "and uname = '" + username + "' and friend = '" + friend + "'";
            cmd.ExecuteReader();
          //  cmd.Clone();
            c.Close();
            return true;
        }

        public bool removeModerator(int forumId, int subForumId, string m_name)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            // remove friend
            cmd.CommandText = "delete from [Moderator] WHERE forumId = " + forumId + "and subForumId = " + subForumId + " and uname = '" + m_name + "'";
            cmd.ExecuteReader();
        //    cmd.Clone();
            c.Close();
            return true;
        }

        public bool removeSubForum(int forumId, int subForumId)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            SqlCeDataReader dr;
            cmd.Connection = c;
            c.Open();
            try
            {
                cmd.CommandText = "delete from [SubForum] WHERE Id = " + subForumId + "and forumId = " + forumId;
                dr = cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [Thread] WHERE forumId = " + forumId + " and subForumId = " + subForumId;
                dr = cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [Post] WHERE forumId = " + forumId + " and subForumId = " + subForumId;
                cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [Moderator] WHERE forumId = " + forumId + " and subForumId = " + subForumId;
                cmd.ExecuteReader();
                dr.Close();
                c.Close();
                return true;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                c.Close();
                return false;
            }

                    


            
        }

 ///////////////////   Getters    //////////////////////////////////
  
        public bool containsMail(int forumId, string email){
                        SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [User] WHERE forumId = " + forumId + " and email = '" + email + "'";
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    dr1.Close();
                    connection.Close();
                //    Console.WriteLine(isMember);
                    return true;
                }
                else {
                    connection.Close();
                    return false;
                }
                
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return false;
            }
        }
        
        public string getForumTitle(int forumId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Forum] WHERE id = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            dr1 = cmd.ExecuteReader();
            //   Console.WriteLine("Connected to database!");
            //add forums
            dr1.Read();
            string title =  (string)dr1["title"]; 
            dr1.Close();
            connection.Close();
            return title;
        }

        public bool isForumUser(int forumId, string user)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [User] WHERE uname = '" + user + "' and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                bool hasUser = dr1.Read();
                dr1.Close();
                connection.Close();
                return hasUser;
              
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return false;
            }
        }

        public bool isMember(int forumId, string username, string password)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [User] WHERE uname = '" + username + "' and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    bool isMember = dr1["password"].Equals(password);
                    dr1.Close();
                    connection.Close();
                //    Console.WriteLine(isMember);
                    return isMember;
                }
                else {
                    connection.Close();
                    return false;
                }
                
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return false;
            }
        }

        public string getLastName(int forumId, string username)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [User] WHERE uname = '" + username + "' and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read()){
                    string lastName = (string)dr1["lastName"];
                    dr1.Close();
                    connection.Close();
                    return lastName;
                }
                else {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public string getEmail(int forumId, string username)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [User] WHERE uname = '" + username + "' and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read()){
                    string email = (string)dr1["email"];
                    dr1.Close();
                    connection.Close();
                    return email;
                }
                else {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }
 
        public SortedDictionary<string, User> getFriends(int forumId, string username)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [UserFriends]";
            dr1 = cmd.ExecuteReader();
            SortedDictionary<string, User> friends = new SortedDictionary<string, User>();
            while (dr1.Read())
            {
                int f_id = (int)dr1["forimId"];
                string friend_name = (string)dr1["friend"];
                User friend = getUser(friend_name, f_id);
                friends.Add(friend_name, friend); 
            }
            dr1.Close();
            connection.Close();
            return friends;
        }

        public int getModeratorComplaints(int forumId, int subForumId, string moderator)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Moderator] WHERE uname = '" + moderator + "' and forumId = " + forumId +
                "and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    int complaint = (int)dr1["complaint"];
                    dr1.Close();
                    connection.Close();
                    return complaint;
                }
                else
                {
                    connection.Close();
                    return 0;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return 0;
            }
        }

        public User getForumAdmin(int forumId) {
   /*         if (dataBase.getForums().ContainsKey(forumId))
                return dataBase.getForums()[forumId].getAdmin();
            else
                return null;
            */

            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Forum] WHERE id = " + forumId;
            cmd.Connection = connection;
            connection.Open();

            //  Console.WriteLine("bbbbb");
            try
            {
             //   Console.WriteLine("bbbbb");
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string admin_name = (string)dr1["admin"];
               
                    dr1.Close();
                    connection.Close();
                    User admin = getUser(admin_name, forumId);
                    //Console.WriteLine(admin.getUserName());
                    return admin;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public int getForumType(int forumId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Forum] WHERE id = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    int type = (int)dr1["type"];
                    dr1.Close();
                    connection.Close();
                    return type;
                }
                else
                {
                    connection.Close();
                    return -1;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return -1;
            }
        }

        public bool containForbiddenWord(int forumId, string word)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [ForbiddenWords] WHERE forumId = " + forumId + " and word = '" + word + "'";
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    dr1.Close();
                    connection.Close();
                    return true;
                }
                else
                {
                    connection.Close();
                    return false;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return false;
            }
        }

        public string getSubForumTitle(int forumId, int subForumId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [SubForum] WHERE Id = " + subForumId + "and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    dr1.Close();
                    connection.Close();
                    return title;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public string getThreadTitle(int forumId, int subForumId, int threadId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Thread] WHERE id = " + threadId + " and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    dr1.Close();
                    connection.Close();
                    return title;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            } 
        }

        public string getThreadBody(int forumId, int subForumId, int threadId) {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Thread] WHERE id = " + threadId + " and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["body"];
                    dr1.Close();
                    connection.Close();
                    return title;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public DateTime getThreadCTime(int forumId, int subForumId, int threadId) 
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Thread] WHERE id = " + threadId + " and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    DateTime title = (DateTime)dr1["creationTime"];
                    dr1.Close();
                    connection.Close();
                    return title;
                }
                else
                {
                    connection.Close();
                    return new DateTime();
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return new DateTime();
            }
        }

        public User getThreadPublishUser(int forumId, int subForumId, int threadId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Thread] WHERE id = " + threadId + " and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string user_name = (string)dr1["user"];
                    dr1.Close();
                    connection.Close();
                    User user = getUser(user_name, forumId);
                    return user;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }   
        }

        public string getPostTitle(int forumId, int subForumId, int threadId, int postId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Post] WHERE id = " + postId + " and forumId = " + forumId + " and subForumId = " + subForumId +
                " and ThreadId = " + threadId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    dr1.Close();
                    connection.Close();
                    return title;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            } 
        }

        public string getPostBody(int forumId, int subForumId, int threadId, int postId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Post] WHERE id = " + postId + " and forumId = " + forumId + " and subForumId = " + subForumId +
                " and ThreadId = " + threadId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string body = (string)dr1["body"];
                    dr1.Close();
                    connection.Close();
                    return body;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public User getPostCreatorUser(int forumId, int subForumId, int threadId, int postId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Post] WHERE id = " + postId + " and forumId = " + forumId + " and subForumId = " + subForumId +
                " and ThreadId = " + threadId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string user_name = (string)dr1["user"];
                    dr1.Close();
                    connection.Close();
                    User user = getUser(user_name, forumId);
                    return user;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }

        }

        public Post getPost(int forumId, int subForumId, int threadId, int postId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Post] WHERE id = " + postId + " and forumId = " + forumId + " and subForumId = " + subForumId +
                " and ThreadId = " + threadId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    string body = (string)dr1["body"];
                    DateTime creationTime = Convert.ToDateTime((string)dr1["creationTime"]);
                    string uname = (string)dr1["user"];
                    User user = getUser(uname, forumId);
                    dr1.Close();
                    connection.Close();
                    Post post = new Post(postId, title, body, user, forumId, subForumId, threadId);
                    post.setCreationTime(creationTime);
                    return post;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public bool isSubForumModerator(int forumId, int subForumId, string user)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Moderator] WHERE uname = '" + user + "' and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    dr1.Close();
                    connection.Close(); 
                    return true;
                }
                else
                {
                    connection.Close();
                    return false;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return false;
            }

        }

        public SortedDictionary<int, Post> getThreadPosts(int forumId, int subForumId, int threadId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Post] WHERE forumId = " + forumId + " and subForumId = " + subForumId +
                " and ThreadId = " + threadId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                SortedDictionary<int, Post> posts = new SortedDictionary<int, Post>();
                while (dr1.Read())
                {
                    int postId = (int)dr1["id"];
                    Post post = getPost(forumId, subForumId, threadId, postId);
                    posts.Add(postId, post);
                }
                dr1.Close();
                connection.Close();
                return posts;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }

        }

        public SortedDictionary<string, User> getSubForumModerators(int forumId, int subForumId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Moderator]  WHERE forumId = " + forumId + "and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                SortedDictionary<string, User> moderators = new SortedDictionary<string, User>();
                while (dr1.Read())
                {
                    string uname = (string)dr1["uname"];
                    User moderator = getUser(uname, forumId);
                    moderators.Add(uname, moderator);
                }
                dr1.Close();
                connection.Close();
                return moderators;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public SortedDictionary<string, int> getSFMComplaints(int forumId, int subForumId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Moderator]  WHERE forumId = " + forumId + "and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                SortedDictionary<string, int> complaints = new SortedDictionary<string, int>();
                while (dr1.Read())
                {
                    string uname = (string)dr1["uname"];
                    int complain = (int)dr1["complaint"];
                    complaints.Add(uname, complain);
                }
                dr1.Close();
                connection.Close();
                return complaints;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public SortedDictionary<int, string> dataBasetoString()
        {
            SortedDictionary<int, string> titles = new SortedDictionary<int, string>();
            
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Forum]";
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    int id = (int)dr1["id"];
                    titles.Add(id, title);
                }
                dr1.Close();
                connection.Close();
                return titles;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return titles;
            }
        }

        public int getNumOfForums()
        {
            int numOfForums = 0;
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Forum]";
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    numOfForums++;
                }
                dr1.Close();
                connection.Close();
                return numOfForums;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return numOfForums;
            }
        }

        public int getidforForums() {
            int numOfForums = 0;
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [DataBase]";
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                    numOfForums = (int)dr1["idforForums"];
                dr1.Close();
                connection.Close();
                return numOfForums;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return numOfForums;
            }
        }
        
        public int getIdForSubForums(int forumId)
        {
            int numOfForums = 0;
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Forum] WHERE id = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                    numOfForums = (int)dr1["idForSubForums"];
                dr1.Close();
                connection.Close();
                return numOfForums;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return numOfForums;
            }
        }

        public int getIdForThreads(int forumId, int subForumId)
        {
            int idForThreads = 0;
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [SubForum] WHERE Id = " + subForumId + " and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                    idForThreads = (int)dr1["idForThreads"];
                dr1.Close();
                connection.Close();
                return idForThreads;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return idForThreads;
            }
        }

        public int getIdForPosts(int forumId, int subForumId, int threadId)
        {
            int idForPosts = 0;
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Thread] WHERE id = " + threadId + " and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                    idForPosts = (int)dr1["idForPosts"];
                dr1.Close();
                connection.Close();
                return idForPosts;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return idForPosts;
            }
        }

        public SortedDictionary<string, User> getForumUsers(int forumId)
        {
            SortedDictionary<string, User> users = new SortedDictionary<string, User>();

            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [User] WHERE  forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    string name = (string)dr1["uname"];
                    User user = getUser(name, forumId);
                    users.Add(name, user);
                }
                dr1.Close();
                connection.Close();
                return users;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return users;
            }
        }

        public SortedDictionary<int, Thread> getSubForumThreads(int forumId, int subForumId) {

            SortedDictionary<int, Thread> threads = new SortedDictionary<int, Thread>();
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Thread] WHERE  forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    int id = (int)dr1["id"];
                    Thread thread = getThread(forumId, subForumId, id);
                    threads.Add(id, thread);
                }
                dr1.Close();
                connection.Close();
                return threads;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message + " in getSubForumThreads)");
                connection.Close();
                return threads;
            }
        }

        public SortedDictionary<string, string> getForbiddenWords(int forumId) { 
           
         SortedDictionary<string, string> words = new SortedDictionary<string, string>();

            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [ForbiddenWords] WHERE  forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    string word = (string)dr1["word"];
                    words.Add(word, word);
                }
                dr1.Close();
                connection.Close();
                return words;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return words;
            }
        }

        public Thread getThread(int forumId, int subForumId, int threadId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Thread] WHERE id = " + threadId + " and forumId = " + forumId + " and subForumId = " + subForumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    string body = (string)dr1["body"];
                    DateTime creationTime = Convert.ToDateTime((string)dr1["creationTime"]);
                    string uname = (string)dr1["user"];
                    User user = getUser(uname, forumId);
                    int idForPosts = (int)dr1["idForPosts"];
                    dr1.Close();
                    Thread thread = new Thread(threadId, title, body, user, forumId, subForumId);
                    thread.setTime(creationTime);
                    SortedDictionary<int, Post> posts = getThreadPosts(forumId, subForumId, threadId);
                    thread.setPosts(posts); 
                    thread.setIdForPosts(idForPosts);
                    connection.Close();
                    return thread;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return null;
            }
        }

        public SubForum getSubForum(int forumId, int subForumId)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [SubForum] WHERE Id = " + subForumId + " and forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    int idForPosts = (int)dr1["idForThreads"];
                    dr1.Close();
                    cmd.CommandText = "SELECT * FROM [Forum] WHERE id = " + forumId;
                    dr1 = cmd.ExecuteReader();
                    dr1.Read();
                    string admin = (string)dr1["admin"];
                    dr1.Close();
                    User sFAdmin = getUser(admin, forumId);
                    SubForum subForum = new SubForum(subForumId, title, forumId, sFAdmin);               
                    SortedDictionary<int, Thread> threads = getSubForumThreads(forumId, subForumId);
                    subForum.setThreads(threads);
                    subForum.setIdForThreads(idForPosts);
                    SortedDictionary<string, User> moderators = getSubForumModerators(forumId, subForumId);
                    subForum.setModerators(moderators);
                    SortedDictionary<string, int> complaints = getSFMComplaints(forumId, subForumId);
                    subForum.setComplaints(complaints); 
                    connection.Close();
                    return subForum;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message + " in getSubForum");
                connection.Close();
                return null;
            } 
        }

        public SortedDictionary<int, SubForum> getSubForums(int forumId)
        {

            SortedDictionary<int, SubForum> subForums = new SortedDictionary<int, SubForum>();
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [SubForum] WHERE  forumId = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    int id = (int)dr1["Id"];
                    SubForum subForum = getSubForum(forumId, id);
                    subForums.Add(id, subForum);
                }
                dr1.Close();
                connection.Close();
                return subForums;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message + " in getSubForums");
                connection.Close();
                return subForums;
            }
        }
       
        public Forum getForum(int forumId) {

            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Forum] WHERE id = " + forumId;
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                if (dr1.Read())
                {
                    string title = (string)dr1["title"];
                    int idForSubForums = (int)dr1["idForSubForums"];
                    string admin = (string)dr1["admin"];
                    int type = (int)dr1["type"];
                    dr1.Close();
                    User fAdmin = getUser(admin, forumId);

                    Forum forum = new Forum(forumId, title, fAdmin, type);
                    SortedDictionary<int, SubForum> subForums = getSubForums(forumId);
                    forum.setSubForums(subForums);
                    SortedDictionary<string, User> users = getForumUsers(forumId);
                    forum.setUsers(users);
                    forum.setIdForSubForums(idForSubForums);
                    connection.Close();
                    return forum;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message + " in getForum");
                connection.Close();
                return null;
            } 
        
        }

        public SortedDictionary<int, Forum> getForums() {

            SortedDictionary<int, Forum> forums = new SortedDictionary<int, Forum>();
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from [Forum]";
            cmd.Connection = connection;
            connection.Open();
            try
            {
                dr1 = cmd.ExecuteReader();
                while (dr1.Read())
                {
                    int id = (int)dr1["id"];
                    Forum forum = getForum(id);
                    forums.Add(id, forum);
                }
                dr1.Close();
                connection.Close();
                return forums;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message + "in getForums");
                connection.Close();
                return forums;
            }
        }


        //add now by zach 21/6/13
        public bool removeForum(int forumid)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            SqlCeDataReader dr;
            cmd.Connection = c;
            c.Open();
            try
            {
                cmd.CommandText = "delete from [Forum] WHERE id = " + forumid;
                dr = cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [SubForum] WHERE forumId = " + forumid;
                dr = cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [Thread] WHERE forumId = " + forumid;
                dr = cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [Post] WHERE forumId = " + forumid;
                cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [Moderator] WHERE forumId = " + forumid;
                cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [User] WHERE forumId = " + forumid;
                cmd.ExecuteReader();
                dr.Close();
                cmd.CommandText = "delete from [ForbiddenWords] WHERE forumId = " + forumid;
                cmd.ExecuteReader();
                dr.Close();
                c.Close();
                return true;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);
                c.Close();
                return false;
            }

        }

        public bool removeUser(int forumid, string username)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            SqlCeDataReader dr;
            cmd.Connection = c;
            c.Open();
            try
            {
                cmd.CommandText = "delete from [User] WHERE uname = '" + username + "' and forumId = " + forumid;
                dr = cmd.ExecuteReader();
                dr.Close();
                c.Close();
                return true;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.Message);     
                c.Close();
                return false;
            }
        }
    }
}
